#数据库备份脚本

# MySQL 连接配置参数
MYSQL_HOST="1.15.117.94"
MYSQL_PORT=3306
MYSQL_USER="root"
MYSQL_PASSWORD="99360..00Xz"

# 备份目录
BACKUP_DIR="/usr/local/mysqlDump"

# 将当前日期作为备份文件名的一部分
CURRENT_DATE=$(date +"%Y-%m-%d")
CURRENT_TIME=$(date +"%Y-%m-%d_%H%M%S")
# 使用 mysqldump 命令备份 MySQL 中所有数据库和表
echo "`date +"%Y-%m-%d_%H:%M:%S"`:-------数据库备份开始-------" >> /usr/local/mysqlDump/log/dump.log
mysqldump --host=${MYSQL_HOST} --port=${MYSQL_PORT} --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --all-databases > ${BACKUP_DIR}/mysql_backup_${CURRENT_TIME}.sql
#                                                                                                               |gzip > ${BACKUP_DIR}/mysql_backup_${CURRENT_TIME}.sql.gz
echo "`date +"%Y-%m-%d_%H:%M:%S"`:-------数据库备份结束-------" >> /usr/local/mysqlDump/log/dump.log

#mysqldump -v --skip-opt  --single-transaction    --host=rm-6zsw529hs7y4y6appyo.mysql.rds.ops.cloud.fjtic.cn --port=3307 --user=ywzt --password='Fjywzt@)@!server2021' --all-databases  |gzip  > /data/mysqlbackup/mysql_backup_pro.sql.gz


#数据库备份脚本2
# 设置MySQL参数
MYSQL_HOST="rm-6zsweap3jx27j95qdgo.mysql.rds.ops.cloud.fjtic.cn"
MYSQL_PORT="3307"
MYSQL_USER="ywzt"
MYSQL_PASSWORD="Fjywzt@)@!server2021"

BACKUP_DIR="/data/mysqlbackup"
CURRENT_TIME=$(date +"%Y-%m-%d_%H%M%S")
BACKUP_FILES_DIR="${BACKUP_DIR}/backupFiles_${CURRENT_TIME}"
NUM=${1:-1440}
EXP="${2:-*.gz}"

echo "---------------------START-----------------------" >> /data/mysqlbackup/log/dump.log
mkdir -p ${BACKUP_FILES_DIR}
echo "备份数据库脚本开始,时间 ${CURRENT_TIME}" >> /data/mysqlbackup/log/dump.log
# 连接MySQL，获取所有数据库名称
DATABASES=$(mysql --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")
# 遍历所有数据库，分别备份
for DB_NAME in $DATABASES; do
  # 备份数据库
  echo "开始备份数据库${DB_NAME}" >> /data/mysqlbackup/log/dump.log
  mysqldump  --skip-opt --single-transaction --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} ${DB_NAME} |gzip > ${BACKUP_FILES_DIR}/${DB_NAME}-${CURRENT_TIME}.sql.gz
done
echo   "所有数据库备份完成,时间 `date +%Y-%m-%d_%H%M%S` " >> /data/mysqlbackup/log/dump.log
#echo   "准备删除${NUM}分钟之前,符合${EXP}的文件" >> /data/mysqlbackup/log/dump.log
#find ${BACKUP_DIR} -type f -name "${EXP}" -mmin +${NUM}  -delete  -printf "删除文件: %p\n" >> /data/mysqlbackup/log/dump.log
echo   "准备删除${NUM}分钟之前,符合${BACKUP_DIR}/backupFiles_*的文件夹" >> /data/mysqlbackup/log/dump.log
find ${BACKUP_DIR} -type d -name "backupFiles_*" -mmin +${NUM} -exec rm -rf {} \; -printf "删除文件: %p\n" >> /data/mysqlbackup/log/dump.log
echo "----------------------END-------------------------" >> /data/mysqlbackup/log/dump.log